﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Xml;
using YnPlcDataServer.Entities;

namespace YnPlcDataServer.Utils
{
    /// <summary>
    /// SQLiteHelper is a utility class similar to "SQLHelper" in MS
    /// Data Access Application Block and follows similar pattern.
    /// </summary>
    public static class SQLiteHelper
    {
        #region 变量定义
        //public static string DataSource = string.Format("Data Source={0};Pooling=true;FailIfMissing=false", Path.Combine(D:\\, "GuessFeng.db"));
        /// <summary>数据库文件</summary>
        private static string dbDir = "MyDb";
        private static string dbFile = "plcdata.db";
        public static int DAQ_ID_1 = 1;
        public static int SECTION_RULE_ID_0 = 1;
        public static int SECTION_RULE_ID_1 = 2;
        public static int SECTION_RULE_TYPE_Condition = 1;
        public static int SECTION_RULE_TYPE_Trigger = 2;
        #endregion

        #region 建立本地数据库
        private static string GetDbPath()
        {
            string _dbPath = System.IO.Path.Combine(Environment.CurrentDirectory, dbDir);
            if (!Directory.Exists(_dbPath))
            {
                Directory.CreateDirectory(_dbPath);
            }
            string dbPath = System.IO.Path.Combine(_dbPath, dbFile);
            return dbPath;
        }
        private static string GetDbConnStr()
        {
            SQLiteConnectionStringBuilder dbConnStr = new SQLiteConnectionStringBuilder();
            dbConnStr.DataSource = GetDbPath();
            dbConnStr.Version = 3;
            return dbConnStr.ToString();
         }
        /// <summary> 
        /// 创建SQLite数据库文件 
        /// </summary> 
        /// <param name="dbPath">要创建的SQLite数据库文件路径</param> 
        public static void CreateDB()
        {
            try 
            {
                //string _dbPath = System.IO.Path.Combine(Environment.CurrentDirectory, dbDir);
                //if (!Directory.Exists(_dbPath))
                //{
                //    Directory.CreateDirectory(_dbPath);
                //}
                //string dbPath = System.IO.Path.Combine(_dbPath, dbFile);
                SQLiteConnection dbConn = null;
                if (!File.Exists(GetDbPath()))
                {
                    SQLiteConnection.CreateFile(GetDbPath());
                }
                dbConn = new SQLiteConnection();
                dbConn.ConnectionString = GetDbConnStr();
                dbConn.Open();//打开数据库，若文件不存在会自动创建

                if (dbConn.State != ConnectionState.Open)
                {
                    throw new Exception("打开数据文件失败！");
                }

                #region YnDaq
                if (!TableExists(dbConn, "yndaq"))
                {
                    //1.创建数据采集器(设备)表
                    string sql = "create table yndaq (";
                    sql += "daq_id int primary key not null,";
                    sql += "createTime datetime,";
                    sql += "updateTime datetime,";
                    sql += "name varchar(50),";
                    sql += "protocol_type varchar(20),";
                    sql += "net_ip varchar(20),";
                    sql += "net_port int,";
                    sql += "net_rack int,";
                    sql += "net_slot int,";

                    sql += "com_port varchar(20),";
                    sql += "com_baudrate int,";
                    sql += "com_databits int,";
                    sql += "com_stopbits int,";
                    sql += "com_parity varchar(10),";
                    sql += "com_station int,";
                    sql += "interval_inseconds int)";
                    using (SQLiteCommand dbComm = new SQLiteCommand(dbConn))
                    {
                        dbComm.CommandText = sql;
                        dbComm.ExecuteNonQuery();
                    }
                }
                #endregion

                #region YnSectionRule
                if (!TableExists(dbConn, "ynsectionrule"))
                {
                    //2.数据断面存储规则表
                    string sql = "create table ynsectionrule (";
                    sql += "rule_id int primary key not null,";
                    sql += "daq_id int,";
                    sql += "svid_id int,";
                    sql += "enabled boolean,";
                    sql += "type int,";
                    sql += "condition_lower_limit numeric(10,5),";
                    sql += "condition_upper_limit numeric(10,5),";
                    sql += "trigger_symbol int,";
                    sql += "trigger_changed numeric(10,5))";
                    using (SQLiteCommand dbComm = new SQLiteCommand(dbConn))
                    {
                        dbComm.CommandText = sql;
                        dbComm.ExecuteNonQuery();
                    }
                }
                #endregion

                #region YnSvid
                if (!TableExists(dbConn, "ynsvid"))
                {
                    //3.状态变量定义表
                    string sql = "create table ynsvid (";
                    sql += "svid_id int primary key not null,";
                    sql += "sortno int,";
                    sql += "createTime datetime,";
                    sql += "updateTime datetime,";
                    sql += "name varchar(50),";
                    sql += "date_type varchar(50),";
                    sql += "address varchar(20),";
                    sql += "length int,";
                    sql += "upper_spec_limit numeric(10,5),";
                    sql += "spec_target numeric(10,5),";
                    sql += "lower_spec_limit numeric(10,5))";
                    using (SQLiteCommand dbComm = new SQLiteCommand(dbConn))
                    {
                        dbComm.CommandText = sql;
                        dbComm.ExecuteNonQuery();
                    }
                }
                #endregion

                dbConn.Close();
                dbConn.Dispose();

                //2.创建变量表
                //string sql = "create table ynDaq (";
                //sql += "id int primary key,";
                //sql += "name varchar(20),";
                //sql += "ip varchar(20),)";
                //sql += "port int)";
                //SQLiteCommand command = new SQLiteCommand(sql, dbConn);
                //command.ExecuteNonQuery();

                /*
                // 自动打开
                if (this.DbConnection == null)
                {
                    this.AutoOpenClose = true;
                    this.Open();
                }
                else if (this.DbConnection.State == ConnectionState.Closed)
                {
                    this.Open();
                }
                this.dbCommand = this.DbConnection.CreateCommand();
                this.dbCommand.CommandText =
                    "CREATE TABLE [DBVersion] ([Version] NVARCHAR(50)  NULL);INSERT INTO [DBVersion] (Version) values('4.0.0.0');CREATE TABLE [RanNum] ([Id] INTEGER PRIMARY KEY NOT NULL,[ExternalId] INTEGER NOT NULL,[Num] INTEGER NOT NULL,[LastQQ]INTEGER NULL);CREATE TABLE [Config] ([Id] INTEGER PRIMARY KEY NOT NULL,[Fix] TEXT NULL,[Open] INTEGER NOT NULL,[Deduct] INTEGER NOT NULL,[Award] INTEGER NOT NULL);INSERT INTO [Config] (Fix,Open,Deduct,Award) values('#',10,5,100);CREATE TABLE [Lock] ([Id] INTEGER PRIMARY KEY NOT NULL,[value] TEXT NULL);";
                this.dbCommand.ExecuteNonQuery();
                //this.dbCommand.CommandText = "DROP TABLE Demo";
                //this.dbCommand.ExecuteNonQuery();*/
                //}
            }
            catch (Exception ex) 
            {
                throw ex;
            }

        }
        /// <summary>  
        /// <para>判断表table是否存在</para>  
        /// </summary>  
        /// <param name="table"></param>  
        /// <returns></returns>  
        private static bool TableExists(SQLiteConnection dbConn, string tableName)
        {
            int recCount = 0;
            try
            {
                using (SQLiteCommand dbComm = new SQLiteCommand(dbConn))
                {
                    dbComm.CommandText = "select count(*) from sqlite_master where type='table' and name=@tableName;";
                    dbComm.Parameters.Add(new SQLiteParameter("tableName", tableName));
                    using (SQLiteDataReader reader = dbComm.ExecuteReader())
                    {
                        reader.Read();
                        recCount = reader.GetInt32(0);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return recCount == 1;
        }  
        #endregion

        #region 查询
        /// <summary>判断DataReader是否存在某列 </summary>
        /// <param name="dr">SqlDataReader</param>
        /// <param name="columnName">列名</param>
        /// <returns></returns>
        private static bool ReaderExists(SQLiteDataReader reader, string columnName)
        {
            reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= '" + columnName + "'";
            return (reader.GetSchemaTable().DefaultView.Count > 0);

        }
        /// <summary>对可空类型进行判断转换(*要不然会报错)</summary>
        /// <param name="value">DataReader字段的值</param>
        /// <param name="conversionType">该字段的类型</param>
        /// <returns></returns>
        private static object CheckType(object value, Type conversionType)
        {
            if (conversionType.IsGenericType && conversionType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
            {
                if (value == null)
                    return null;
                System.ComponentModel.NullableConverter nullableConverter = new System.ComponentModel.NullableConverter(conversionType);
                conversionType = nullableConverter.UnderlyingType;
            }
            return Convert.ChangeType(value, conversionType);
        }
        /// <summary>判断指定对象是否是有效值</summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        private static bool IsNullOrDBNull(object obj)
        {
            return (obj == null || (obj is DBNull)) ? true : false;
        }
        /// <summary>DataReader转模型</summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="objReader"></param>
        /// <returns></returns>
        private static T ReaderToModel<T>(this IDataReader objReader)
        {

            using (objReader)
            {
                if (objReader.Read())
                {
                    Type modelType = typeof(T);
                    int count = objReader.FieldCount;
                    T model = Activator.CreateInstance<T>();
                    for (int i = 0; i < count; i++)
                    {
                        if (!IsNullOrDBNull(objReader[i]))
                        {
                            PropertyInfo pi = modelType.GetProperty(objReader.GetName(i), BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);
                            if (pi != null)
                            {
                                pi.SetValue(model, CheckType(objReader[i], pi.PropertyType), null);
                            }
                        }
                    }
                    return model;
                }
            }
            return default(T);
        }
        ///<summary>利用反射和泛型将DataReader转换成List模型</summary>
        ///<param name="sql">查询sql语句</param>
        ///<returns></returns>
        public static IList<T> Query<T>(string sql) where T : new()
        {
            IList<T> list = new List<T>();
            Type type = typeof(T);
            string tempName = string.Empty;
            try
            {
                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    using (SQLiteCommand dbComm = dbConn.CreateCommand())
                    {
                        dbComm.CommandType = CommandType.Text;
                        dbComm.CommandText = sql;// "select * from ";// +TableConvention.Resolve(typeof(T)) + " where ".InjectFrom(new FieldsBy().SetFormat("{0}=@{0}").SetNullFormat("{0} is null").SetGlue("and"), where);
                        //cmd.InjectFrom<SetParamsValues>(where);
                        dbConn.Open();
                        using (SQLiteDataReader reader = dbComm.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                //list = new List<T>();
                                while (reader.Read())
                                {
                                    T t = new T();
                                    PropertyInfo[] propertys = t.GetType().GetProperties();
                                    foreach (PropertyInfo pi in propertys)
                                    {
                                        tempName = pi.Name;
                                        if (ReaderExists(reader, tempName))
                                        {
                                            if (!pi.CanWrite)
                                            {
                                                continue;
                                            }
                                            var value = reader[tempName];
                                            if (value != DBNull.Value)
                                            {
                                                pi.SetValue(t, value, null);
                                            }
                                        }
                                    }
                                    list.Add(t);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        public static object GetObject(string sql)
        {
            object object0 = null;
            try
            {
                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    using (SQLiteCommand dbComm = dbConn.CreateCommand())
                    {
                        dbComm.CommandText = sql;
                        using (SQLiteDataReader reader = dbComm.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                object0 = reader.GetValue(0);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return object0;
        }
        /// <summary>获取最大编号</summary>
        /// <typeparam name="T">实体Model</typeparam>
        /// <param name="key">主键</param>
        public static int GetMaxID<T>(string key)
        {
            int maxId = 0;
            Type type = typeof(T);
            try
            {
                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    using (SQLiteCommand dbComm = dbConn.CreateCommand())
                    {
                        dbComm.CommandText = string.Format("SELECT Max({0}) FROM {1}", key, type.Name);
                        object obj = dbComm.ExecuteScalar();
                        if (!(Object.Equals(obj, null)) && (!Object.Equals(obj, System.DBNull.Value)))
                            maxId = Convert.ToInt32(obj);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return maxId + 1;
        }
        #endregion

        #region Dap
        public static YnDaq GetYnDaq(int daq_id)
        {
            YnDaq ynDaq = null;
            try
            {
                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    using (SQLiteCommand dbComm = dbConn.CreateCommand())
                    {
                        dbComm.CommandText = "select * from YnDaq where daq_id=" + daq_id + ";";
                        using (SQLiteDataReader reader = dbComm.ExecuteReader())
                        {
                            //if (reader.Read())
                            //{
                                //throw new Exception("不存在数据采集器\"" + daq_id + "\"！");
                                ynDaq = ReaderToModel<YnDaq>(reader);
                            //}
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return ynDaq;
        }
        #endregion

        #region SectionRule
        /*
        public static YnSectionRule GetYnSectionRule(int rule_id)
        {
            YnSectionRule ynSectionRule = null;
            try
            {
                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    using (SQLiteCommand dbComm = dbConn.CreateCommand())
                    {
                        dbComm.CommandText = "select * from YnSectionRule where rule_id=" + rule_id + ";";
                        using (SQLiteDataReader reader = dbComm.ExecuteReader())
                        {
                            //if (reader.Read())
                            //{
                            //throw new Exception("不存在数据采集器\"" + daq_id + "\"！");
                            ynSectionRule = ReaderToModel<YnSectionRule>(reader);
                            //}
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return ynSectionRule;
        }*/
        #endregion

        #region SvData
        public static void AddHistoryData(int svid_id, string value,DateTime dtCurrent)
        {
            try
            {
                //历史数据分日建表
                DateTime dtServerTime = DateTime.Now;
                string tableName = "ynsvdata_" + dtServerTime.ToString("yyyyMMdd");
                //string tableName_old = "ynsvdata_" + dtServerTime.AddDays(-25).ToString("yyyyMMdd");//删除50天前的表
                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    //建表
                    if (!TableExists(dbConn, tableName))
                    {

                        //删除表
                        for (int iday = 0; iday < 100; iday++)
                        {
                            string tableName_old = "ynsvdata_" + dtServerTime.AddDays(-50 - iday).ToString("yyyyMMdd");//删除50天前的表
                            //超过100天不运行估计比较少了
                            if (TableExists(dbConn, tableName_old))
                            {
                                //1.创建数据采集器(设备)表
                                string sql_delete = "drop table " + tableName_old + ";";
                                using (SQLiteCommand dbComm = new SQLiteCommand(dbConn))
                                {
                                    dbComm.CommandText = sql_delete;
                                    dbComm.ExecuteNonQuery();
                                }
                            }
                        }

                        //1.创建数据采集器(设备)表
                        string sql = "create table " + tableName + " (";
                        sql += "data_id integer primary key autoincrement,";// integer PRIMARY KEY autoincrement, 
                        sql += "svid_id int not null,";
                        sql += "createTime datetime,";
                        sql += "value varchar(50))";
                        using (SQLiteCommand dbComm = new SQLiteCommand(dbConn))
                        {
                            dbComm.CommandText = sql;
                            dbComm.ExecuteNonQuery();
                        }
                    }


                    //插入
                    //string sql_insert = string.Format("insert into {0}(svid_id,createTime,value) values ({1},datetime('now','localtime'),{2})",
                        //tableName, svid_id, value);
                    string sql_insert = string.Format("insert into {0}(svid_id,createTime,value) values ({1},datetime('"+dtCurrent.ToString("yyyy-MM-dd HH:mm:ss")+"') ,{2})",
                        tableName, svid_id, value);

                    using (SQLiteCommand dbComm = dbConn.CreateCommand())
                    {
                        dbComm.CommandText = sql_insert;
                        int result = dbComm.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static int GetHistoryDataCount(DateTime dtStart, DateTime dtEnd, List<int> listId)
        {
            int count = 0;
            try
            {
                string idList = string.Join(",", listId);
                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    DateTime dtTmp = dtStart;
                    for (dtTmp = dtStart; dtTmp <= dtEnd; dtTmp = Convert.ToDateTime(dtTmp.AddDays(1).ToString("yyyy-MM-dd")))
                    {
                        string tableName = "ynsvdata_" + dtTmp.ToString("yyyyMMdd");
                        if (!TableExists(dbConn, tableName))
                            continue;
                        using (SQLiteCommand dbComm = dbConn.CreateCommand())
                        {
                            dbComm.CommandText = "select count(*) from " + tableName + " where createTime>='" + dtTmp.ToString("yyyy-MM-dd HH:mm:ss") + "' and createTime<'" + dtEnd.ToString("yyyy-MM-dd HH:mm:ss") + "' and svid_id in (" + idList + ");";
                            using (SQLiteDataReader reader = dbComm.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    count += Convert.ToInt32(reader.GetValue(0));
                                }
                            }
                        }
                    }

                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return count;
        }
        public static List<YnSvData> QueryHistoryData(ref DateTime dtStart, DateTime dtEnd, List<int> listId)
        {
            List<YnSvData> list = new List<YnSvData>();
            try
            {
                int count = 300;
                string idList = string.Join(",", listId);
                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    IList<YnSvid> ilistYnSvid = SQLiteHelper.Query<YnSvid>("select * from YnSvid");
                    DateTime dtTmp = dtStart;
                    //for (dtTmp = dtStart; dtTmp <= dtEnd; dtTmp = dtTmp = Convert.ToDateTime(dtTmp.AddDays(1).ToString("yyyy-MM-dd")))
                    //{
                        int recCount=0;
                        string tableName = "ynsvdata_" + dtStart.ToString("yyyyMMdd");
                        if (TableExists(dbConn, tableName))
                        {
                            using (SQLiteCommand dbComm = dbConn.CreateCommand())
                            {
                                dbComm.CommandText = "select * from " + tableName + " where createTime>='" + dtStart.ToString("yyyy-MM-dd HH:mm:ss") + "' and createTime<'" + dtEnd.ToString("yyyy-MM-dd HH:mm:ss") + "' and svid_id in (" + idList + ") order by createTime limit 0," + count + ";";//每次300行
                                using (SQLiteDataReader reader = dbComm.ExecuteReader())
                                {
                                    if (reader.HasRows)
                                    {
                                        while (reader.Read())
                                        {
                                            recCount++;
                                            YnSvData ynSvData = new YnSvData();
                                            PropertyInfo[] propertys = ynSvData.GetType().GetProperties();
                                            foreach (PropertyInfo pi in propertys)
                                            {
                                                string tempName = pi.Name;
                                                if (pi.Name.ToLower().Equals("ynsvid"))
                                                    continue;
                                                if (ReaderExists(reader, tempName))
                                                {
                                                    if (!pi.CanWrite)
                                                    {
                                                        continue;
                                                    }
                                                    var value = reader[tempName];
                                                    if (value != DBNull.Value)
                                                    {
                                                        pi.SetValue(ynSvData, value, null);
                                                    }
                                                }
                                            }
                                            dtStart = ynSvData.createTime.AddSeconds(1);
                                            ynSvData.ynSvid = ilistYnSvid.ToList().Find(item => item.svid_id == ynSvData.svid_id);
                                            list.Add(ynSvData);
                                        }
                                    }
                                }
                            }
                        }
                        //如果没有查到任何记录，要处理一下
                        if(recCount==0)
                        {
                            dtStart = Convert.ToDateTime(dtTmp.AddDays(1).ToString("yyyy-MM-dd"));
                        }
                    //}
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        #endregion

        #region 执行带参数的SQL语句
        /// <summary>
        /// 添加
        /// </summary>
        //public static void Insert(object obj, bool autoIncrement)
        //{
        //StringBuilder strSql = new StringBuilder();
        //Type type = obj.GetType();
        //strSql.Append(string.Format("insert into {0}(", type.Name));

        //PropertyInfo[] propertyInfoList = GetEntityProperties(type);
        //List<string> propertyNameList = new List<string>();
        //foreach (PropertyInfo propertyInfo in propertyInfoList)
        //{
        //     if（propertyInfo.Name.ToLower().Equals()&& autoIncrement) return;
        //    )
        //    {
        //        propertyNameList.Add(propertyInfo.Name);
        //        savedCount++;
        //    }
        //}

        //strSql.Append(string.Format("{0})", string.Join(",", propertyNameList.ToArray())));
        //strSql.Append(string.Format(" values ({0})", string.Join(",", propertyNameList.ConvertAll<string>(a => m_ParameterMark + a).ToArray())));
        //DbParameter[] parameters = new DbParameter[savedCount];
        //;
        //; i < propertyInfoList.Length && savedCount > ; i++)
        //{
        //    PropertyInfo propertyInfo = propertyInfoList[i];
        //     && autoIncrement) return;
        //    )
        //    {
        //        object val = propertyInfo.GetValue(obj, null);
        //        DbParameter param = GetDbParameter(m_ParameterMark + propertyInfo.Name, val == null ? DBNull.Value : val);
        //        parameters[k++] = param;
        //    }
        //}

        //ExecuteSql(strSql.ToString(), parameters);
        //}
        /// <summary>添加</summary>
        public static int Insert(object obj)
        {
            int result = 0;
            try
            {
                //var str = _type == DbContextType.MySql ? "?" : "@";
                Type objType = obj.GetType();
                //var str = "?";
                var data = objType.GetProperties().ToDictionary(p => p.Name, p => p.GetValue(obj, null));
                string sql = string.Format("INSERT INTO {0}({1}) VALUES (@{2})",
                    objType.Name, string.Join(",", data.Keys), string.Join(",@", data.Keys));//C# Sqlite 不能使用参数前缀”?”, 换成”@”就
                //string sql = string.Format("INSERT INTO {0}({2}) VALUES ({1}{3})",
                //type.Name, str, string.Join(",", data.Keys), string.Join("," + str, data.Keys));
                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    using (SQLiteCommand dbComm = dbConn.CreateCommand())
                    {
                        dbComm.CommandText = sql;
                        using (SQLiteTransaction trans = dbConn.BeginTransaction())
                        {
                            try
                            {
                                dbComm.Transaction = trans;

                                foreach (string key in data.Keys)
                                {
                                    var p = dbComm.CreateParameter();
                                    p.ParameterName = key;
                                    p.Value = data[key] ?? DBNull.Value;
                                    dbComm.Parameters.Add(p);
                                }
                                result = dbComm.ExecuteNonQuery();
                                trans.Commit();
                            }
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                throw ex;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
 

            return result;
        }
        /// <summary>修改</summary>
        public static int Update(object values, object where)
        {
            Type objType = values.GetType();
            int result = 0;

            try
            {
                //var str = _type == DbContextType.MySql ? "?" : "@";
                var fields = values.GetType().GetProperties()
                    .ToDictionary(p => p.Name, p => p.GetValue(values, null));

                var sb = new StringBuilder();
                sb.AppendFormat("UPDATE {0} ", objType.Name);
                sb.AppendFormat("SET {0} ", string.Join(", ",
                    fields.Keys.ToList().ConvertAll<string>(key => key + "=@" + key).ToArray()));//C# Sqlite 不能使用参数前缀”?”, 换成”@”就

                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    using (SQLiteCommand dbComm = dbConn.CreateCommand())
                    {
                        foreach (string key in fields.Keys)
                        {
                            var p = dbComm.CreateParameter();
                            p.ParameterName = key;
                            p.Value = fields[key] ?? DBNull.Value;
                            dbComm.Parameters.Add(p);
                        }
                        if (where != null)
                        {
                            sb.Append("WHERE ");

                            if (where is String)
                            {
                                sb.Append(where.ToString());
                            }
                            else
                            {
                                var wheres = where.GetType().GetProperties()
                                    .ToDictionary(p => p.Name, p => p.GetValue(where, null));

                                sb.Append(string.Join(", ",  // eg: field1=@_field1;
                                 wheres.Keys.ToList().ConvertAll<string>(key => key + "=@_" + key).ToArray()));

                                foreach (string key in wheres.Keys)
                                {
                                    var p = dbComm.CreateParameter();
                                    p.ParameterName = "_" + key;
                                    p.Value = wheres[key] ?? DBNull.Value;
                                    dbComm.Parameters.Add(p);
                                }

                            }
                        }
                        dbComm.CommandText = sb.ToString();

                        using (SQLiteTransaction trans = dbConn.BeginTransaction())
                        {
                            try
                            {
                                dbComm.Transaction = trans;

                                result = dbComm.ExecuteNonQuery();
                                trans.Commit();
                            }
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                throw ex;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }
        /// <summary>删除</summary>
        public static int Delete(object obj, object where)
        {
            Type objType = obj.GetType();
            int result = 0;
            try
            {

                var sb = new StringBuilder();
                sb.AppendFormat("delete from {0} ", objType.Name);

                using (SQLiteConnection dbConn = new SQLiteConnection(GetDbConnStr()))
                {
                    dbConn.Open();
                    using (SQLiteCommand dbComm = dbConn.CreateCommand())
                    {
                        if (where != null)
                        {
                            sb.Append("where ");

                            if (where is String)
                            {
                                sb.Append(where.ToString());
                            }
                            else
                            {
                                var wheres = where.GetType().GetProperties()
                                    .ToDictionary(p => p.Name, p => p.GetValue(where, null));

                                sb.Append(string.Join(", ",  // eg: field1=@_field1;
                                 wheres.Keys.ToList().ConvertAll<string>(key => key + "=@_" + key).ToArray()));

                                foreach (string key in wheres.Keys)
                                {
                                    var p = dbComm.CreateParameter();
                                    p.ParameterName = "_" + key;
                                    p.Value = wheres[key] ?? DBNull.Value;
                                    dbComm.Parameters.Add(p);
                                }

                            }
                        }
                        dbComm.CommandText = sb.ToString();

                        using (SQLiteTransaction trans = dbConn.BeginTransaction())
                        {
                            try
                            {
                                dbComm.Transaction = trans;

                                result = dbComm.ExecuteNonQuery();
                                trans.Commit();
                            }
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                throw ex;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return result;
        }
        #endregion

    }
}
